package com.flappySeeker.controller;

public class Sql {
	
	
	/**
	 * 最大条数限制  已支付
	 */
	public final static int PRO_MAX  =20; 
	/**
	 * 最小条数限制 未支付
	 */
	public final static int PRO_MIN  =10; 
	
	/**
	 * 最大条数限制  已支付
	 */
	public final static int UN_MAX =10; 
	/**
	 * 最小条数限制 未支付
	 */
	public final static int UN_MIN =3; 
	
	public final static String UNVERSITY  ="unversity"; 
	public final static String MBTI  ="mbti"; 
	public final static String HOLLAND  ="holland";
	
	/**
	 * 保存用户
	 * 12
	 */
	public final static String SAVE_USER ="INSERT INTO tb_userinfo (userid,username,usersex,usertel,useremail,incity,inprovince,score,ispay,isclicked,resultsurl,verificode) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
	
	/**
	 * 匹配专业前多少条
	 * 1mbti
	 * 2holland
	 * 3前多少条
	 */
	public final static String TOP_PROFESSIONAL ="select * from tb_professional where mbti like ? and holland like ? ORDER BY professionalranks ASC LIMIT ? ";
	/**
	 * 匹配专业下课程前多少条
	 * 1 in专业
	 * 2 前多少条 
	 */
	public final static String TOP_UNIVERSITY_IN= "SELECT DISTINCT(u.universityid),u.universityname,u.indepartment,u.inprovinces,u.educationlevel,u.schoolclass,u.content,u.sort FROM tb_university u,tb_universityprofessional t WHERE u.universityid=t.universityid AND t.professionalid IN (?) ORDER BY u.sort ASC LIMIT ?";
	
	/**
	 * 匹配专业下学校前多少条
	 * 1 in专业
	 * 2 前多少条 
	 */
	public final static String TOP_UNIVERSITY_PRO= "SELECT u.* FROM tb_university u,tb_universityprofessional t WHERE u.universityid=t.universityid AND t.professionalid =? ORDER BY u.sort ASC LIMIT ?";
	/**
	 * 匹配专业下学校前多少条
	 * 1 in专业
	 * 2 本地
	 * 3 前多少条 
	 */
	public final static String TOP_UNIVERSITY_PRO_LOCAL= "SELECT u.* FROM tb_university u,tb_universityprofessional t WHERE u.universityid=t.universityid AND inprovinces =? AND t.professionalid =? ORDER BY u.sort ASC LIMIT ?";
	
	/**
	 * 匹配专业下学校前多少条
	 * 1 in专业
	 * 2 外地 
	 * 3 前多少条 
	 */
	public final static String TOP_UNIVERSITY_PRO_NOLOCAL= "SELECT u.* FROM tb_university u,tb_universityprofessional t WHERE u.universityid=t.universityid AND inprovinces !=? AND t.professionalid =? ORDER BY u.sort ASC LIMIT ?";
	
	
	/**
	 * 根据MBTI查询对应专业
	 * 1 mbti 模糊查询
	 * 2  前多少条
	 */
	public final static String PROFESSIONAL_MBTI ="select * from tb_professional where mbti like ? LIMIT ? ";
	/**
	 * 根据MBTI查询对应专业条数
	 * 1 mbti 模糊查询
	 */
	public final static String PROFESSIONAL_MBTI_COUNT ="SELECT count(1) FROM tb_professional WHERE mbti LIKE ?";
	
	/**
	 * 根据MBTI 和 HOLLAND综合查询对应专业
	 * 1 mbti 模糊查询
	 * 2 holland
	 * 3  前多少条
	 */
	public final static String PROFESSIONAL_COMPREHENSIVE  ="SELECT p.* FROM (SELECT * FROM tb_professional WHERE mbti LIKE ? UNION SELECT * FROM tb_professional WHERE holland LIKE ?) p  LIMIT ?";
	
	/**
	 * 根据MBTI 和 HOLLAND综合查询对应专业条数
	 * 1 mbti 模糊查询
	 * 2 holland
	 */
//	public final static String PROFESSIONAL_COMPREHENSIVE_COUNT  ="select count(1) from tb_professional where mbti like ? OR holland like ?  ";
	
	public final static String PROFESSIONAL_COMPREHENSIVE_COUNT  ="SELECT SUM(p.c) total FROM (SELECT count(1) c FROM tb_professional WHERE mbti like ? UNION SELECT count(1) c FROM tb_professional WHERE holland like ?) p";
	
	
	/**
	 * 根据HOLLAND查询对应专业
	 * 1 HOLLAND 模糊查询
	 * 2  前多少条
	 */
	public final static String PROFESSIONAL_HOLLAND ="SELECT * FROM tb_professional WHERE holland LIKE ? LIMIT ? ";
	
	/**
	 * 根据HOLLAND查询对应专业 总条数
	 * 1 HOLLAND 模糊查询
	 */
	public final static String PROFESSIONAL_HOLLAND_COUNT ="SELECT count(1) FROM tb_professional WHERE holland LIKE ?";

	/**
	 * 根据专业ID查询学校
	 * 1 专业
	 * 2 前多少条 
	 */
	public final static String UNIVERSITY_BYPRO= "SELECT u.* FROM tb_university u,tb_universityprofessional t WHERE u.universityid=t.universityid AND t.professionalid =? LIMIT ?";
	
	/**
	 * 根据userID查询mbti 答案
	 * 1 userid
	 */
	public final static String MBTI_ANSWER ="SELECT mbtians FROM tb_userinfo WHERE userid=?";
	
	/**
	 * 根据userID更新mbti 答案
	 * 1 mbti 答案
	 * 2 userid
	 */
	public final static String UPMBTI_ANSWER ="UPDATE tb_userinfo SET mbtians =? WHERE userid=?";
	
	/**
	 * 根据userID更新测试报告地址
	 * 1 url 报告地址
	 * 2 userid
	 */
	public final static String UP_RESULTSURL ="UPDATE tb_userinfo SET resultsurl =? WHERE userid=?";
	
	
	/**
	 * 根据userID清空序列号
	 * 1 userid
	 */
	public final static String UP_VERIFICODE ="UPDATE tb_userinfo SET verificode ='' WHERE userid=?";
	
	
	/**
	 * 根据userID查询mbti 结果地址
	 * 1 userid
	 */
	public final static String MBTI_RESULTSURL ="SELECT resultsurl FROM tb_userinfo WHERE userid=?";
	
	//public final static String UNIVERSITY_BYPRO= "SELECT u.* FROM tb_university u,tb_universityprofessional t WHERE u.universityid=t.universityid AND t.professionalid =? ORDER BY u.sort ASC LIMIT ?";
	
	public static void main(String[] args) {
		String s=TOP_UNIVERSITY_PRO;
		s.replace("?", "dd");
	}
}
